﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
using PhotoGallery.Infrastructure.Collections;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class AccountProvider : PhotoGallery.Core.Providers.DALProvider.IAccountProvider
    {
        #region Sql
        private static readonly string SqlSelectAccount = "SELECT [Id], [Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath] FROM [Account] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertAccount = "INSERT INTO [Account]([Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath]) values(@Status,@Birthday,@Gender,@Name,@Telephone,@Address,@LoginName,@Password,@AvatarPath);SELECT SCOPE_IDENTITY();";

        private static readonly string SqlUpdateAccount = "UPDATE [Account] SET [Status] = @Status,[Birthday] = @Birthday,[Gender] = @Gender,[Name] = @Name,[Telephone] = @Telephone,[Address] = @Address,[LoginName] = @LoginName,[Password] = @Password,[AvatarPath] = @AvatarPath WHERE  [Id]=@Id;";

        private static readonly string SqlDeleteAccount = "DELETE [Account] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllAccount = "SELECT [Id], [Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath] FROM [Account] ;";



        private static readonly string SqlSerchAccount = "SELECT [Id], [Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath] FROM (  SELECT *,row_number() over(order by name desc) as rownum FROM [Account] "
                                                        + " WHERE ([Name] like '%'+@Name+'%' or @Name is null) "
                                                        + " and ([Gender] =@Gender or @Gender is null) ) innerSelect WHERE rownum >@start and rownum<@end ; select @totalcount= count(*) from Account where ([Name] like '%'+@Name+'%' or @Name is null) and ([Gender] =@Gender or @Gender is null) ";

        private static readonly string SqlFindByName = " SELECT  [Id], [Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath] FROM [Account] WHERE  [Name]=@Name ;";

        private static readonly string SqlFindBy = " SELECT  [Id], [Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath] FROM [Account] WHERE  [LoginName]=@LoginName and [Password]=@Password ;";

        #endregion

        #region Procedures
        /*

-- Begin of Account
IF OBJECT_ID(N'[USP_Account_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Account_Delete_By_Id]

IF OBJECT_ID(N'[USP_Account_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Account_Select_By_Id]

IF OBJECT_ID(N'[USP_Account_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_Account_Insert]

IF OBJECT_ID(N'[USP_Account_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_Account_Update_By_Id]

IF OBJECT_ID(N'[USP_Account_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_Account_SelectAll]

GO

--region [USP_Account_Delete_By_Id]
CREATE PROCEDURE [USP_Account_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [Account] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Account_Select_By_Id]
CREATE PROCEDURE [USP_Account_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath] FROM [Account] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Account_Insert]
CREATE PROCEDURE [USP_Account_Insert]
	@Status	int,
	@Birthday	datetime,
	@Gender	decimal,
	@Name	nvarchar(50),
	@Telephone	nvarchar(30),
	@Address	nvarchar(100),
	@LoginName	nvarchar(50),
	@Password	nvarchar(50),
	@AvatarPath	nvarchar(100)
AS
SET NOCOUNT ON
INSERT INTO [Account]([Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath]) values(@Status,@Birthday,@Gender,@Name,@Telephone,@Address,@LoginName,@Password,@AvatarPath);SELECT SCOPE_IDENTITY();
--endregion
GO

--region [USP_Account_Update_By_Id]
CREATE PROCEDURE [USP_Account_Update_By_Id]
	@Status	int,
	@Birthday	datetime,
	@Gender	decimal,
	@Name	nvarchar(50),
	@Telephone	nvarchar(30),
	@Address	nvarchar(100),
	@LoginName	nvarchar(50),
	@Password	nvarchar(50),
	@AvatarPath	nvarchar(100),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [Account] SET [Status] = @Status,[Birthday] = @Birthday,[Gender] = @Gender,[Name] = @Name,[Telephone] = @Telephone,[Address] = @Address,[LoginName] = @LoginName,[Password] = @Password,[AvatarPath] = @AvatarPath WHERE  [Id]=@Id;
--endregion
GO

--region [USP_Account_SelectAll]
CREATE PROCEDURE [USP_Account_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [Status],[Birthday],[Gender],[Name],[Telephone],[Address],[LoginName],[Password],[AvatarPath] FROM [Account];
--endregion
GO


-- End of Account
*/
        #endregion

        #region IAccountProvider Members
        public Core.Business.Account Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectAccount);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Account account = new Core.Business.Account();

                if (!reader.IsDBNull(0)) account.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) account.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) account.Birthday = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) account.Gender = reader.GetDecimal(3);
                if (!reader.IsDBNull(4)) account.Name = reader.GetString(4);
                if (!reader.IsDBNull(5)) account.Telephone = reader.GetString(5);
                if (!reader.IsDBNull(6)) account.Address = reader.GetString(6);
                if (!reader.IsDBNull(7)) account.LoginName = reader.GetString(7);
                if (!reader.IsDBNull(8)) account.Password = reader.GetString(8);
                if (!reader.IsDBNull(9)) account.AvatarPath = reader.GetString(9);

                reader.Close();
                return account;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.Account account)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, account.Status);
            sql.AddParameter("@Birthday", SqlDbType.DateTime, account.Birthday);
            sql.AddParameter("@Gender", SqlDbType.Decimal, account.Gender);
            sql.AddParameter("@Name", SqlDbType.NVarChar, account.Name);
            sql.AddParameter("@Telephone", SqlDbType.NVarChar, account.Telephone);
            sql.AddParameter("@Address", SqlDbType.NVarChar, account.Address);
            sql.AddParameter("@LoginName", SqlDbType.NVarChar, account.LoginName);
            sql.AddParameter("@Password", SqlDbType.NVarChar, account.Password);
            sql.AddParameter("@AvatarPath", SqlDbType.NVarChar, account.AvatarPath);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlInsertAccount);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                if (!reader.IsDBNull(0)) account.Id = int.Parse(reader.GetDecimal(0).ToString());

                reader.Close();
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }
        }

        public void Update(Core.Business.Account account)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Status", SqlDbType.Int, account.Status);
            sql.AddParameter("@Birthday", SqlDbType.DateTime, account.Birthday);
            sql.AddParameter("@Gender", SqlDbType.Decimal, account.Gender);
            sql.AddParameter("@Name", SqlDbType.NVarChar, account.Name);
            sql.AddParameter("@Telephone", SqlDbType.NVarChar, account.Telephone);
            sql.AddParameter("@Address", SqlDbType.NVarChar, account.Address);
            sql.AddParameter("@LoginName", SqlDbType.NVarChar, account.LoginName);
            sql.AddParameter("@Password", SqlDbType.NVarChar, account.Password);
            sql.AddParameter("@AvatarPath", SqlDbType.NVarChar, account.AvatarPath);
            sql.AddParameter("@Id", SqlDbType.Int, account.Id);
            sql.ExecuteSql(SqlUpdateAccount);
        }

        public void Delete(Core.Business.Account account)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, account.Id);
            sql.ExecuteSql(SqlDeleteAccount);
        }

        public IList<Core.Business.Account> GetAllAccount()
        {
            IList<Core.Business.Account> accountlist = new List<Core.Business.Account>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllAccount);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Account account = new Core.Business.Account();

                    if (!reader.IsDBNull(0)) account.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) account.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) account.Birthday = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) account.Gender = reader.GetDecimal(3);
                    if (!reader.IsDBNull(4)) account.Name = reader.GetString(4);
                    if (!reader.IsDBNull(5)) account.Telephone = reader.GetString(5);
                    if (!reader.IsDBNull(6)) account.Address = reader.GetString(6);
                    if (!reader.IsDBNull(7)) account.LoginName = reader.GetString(7);
                    if (!reader.IsDBNull(8)) account.Password = reader.GetString(8);
                    if (!reader.IsDBNull(9)) account.AvatarPath = reader.GetString(9);

                    account.MarkOld();
                    accountlist.Add(account);
                }
                reader.Close();
            }
            return accountlist;
        }
        #endregion


        public AccountIndexModel Search(AccountSearch packet)
        {
            if (packet == null)
            {
                throw new ArgumentException("packet for search is null!!!!");

            }



            IList<Core.Business.Account> accountlist = new List<Core.Business.Account>();

            SqlServerUtility sql = new SqlServerUtility();

            int start = packet.PageIndex.HasValue ? packet.PageIndex.Value - 1 : 0 * (packet.PageSize.HasValue ? packet.PageSize.Value : 10);
            int end = start + (packet.PageSize.HasValue ? packet.PageSize.Value : 10);

            sql.AddParameter("@Name", SqlDbType.NVarChar, !string.IsNullOrEmpty(packet.Name) ? (object)packet.Name : DBNull.Value, 30);
            sql.AddParameter("@Gender", SqlDbType.Int, packet.Gender != Core.Business.Gender.Female && packet.Gender != Core.Business.Gender.Male ? DBNull.Value : (object)packet.Gender);
            sql.AddParameter("@start", SqlDbType.Int, start);
            sql.AddParameter("@end", SqlDbType.Int, end);
            sql.AddOutputParameter("@totalCount", SqlDbType.Int);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlSerchAccount);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.Account account = new Core.Business.Account();

                    if (!reader.IsDBNull(0)) account.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) account.Status = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) account.Birthday = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) account.Gender = reader.GetDecimal(3);
                    if (!reader.IsDBNull(4)) account.Name = reader.GetString(4);
                    if (!reader.IsDBNull(5)) account.Telephone = reader.GetString(5);
                    if (!reader.IsDBNull(6)) account.Address = reader.GetString(6);
                    if (!reader.IsDBNull(7)) account.LoginName = reader.GetString(7);
                    if (!reader.IsDBNull(8)) account.Password = reader.GetString(8);
                    if (!reader.IsDBNull(9)) account.AvatarPath = reader.GetString(9);

                    account.MarkOld();
                    accountlist.Add(account);
                }
                reader.Close();
            }

            AccountIndexModel model = new AccountIndexModel(packet, new PagedList<Account>(accountlist, packet.PageIndex.Value, packet.PageSize.Value, 10));

            return model;
        }


        public Account FindByName(string name)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Name", SqlDbType.NVarChar, name);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlFindByName);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Account account = new Core.Business.Account();

                if (!reader.IsDBNull(0)) account.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) account.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) account.Birthday = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) account.Gender = reader.GetDecimal(3);
                if (!reader.IsDBNull(4)) account.Name = reader.GetString(4);
                if (!reader.IsDBNull(5)) account.Telephone = reader.GetString(5);
                if (!reader.IsDBNull(6)) account.Address = reader.GetString(6);
                if (!reader.IsDBNull(7)) account.LoginName = reader.GetString(7);
                if (!reader.IsDBNull(8)) account.Password = reader.GetString(8);
                if (!reader.IsDBNull(9)) account.AvatarPath = reader.GetString(9);

                reader.Close();
                return account;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }


        public Account FindByName(string loginName, string password)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@LoginName", SqlDbType.NVarChar, loginName);
            sql.AddParameter("@Password", SqlDbType.NVarChar, password);

            SqlDataReader reader = sql.ExecuteSqlReader(SqlFindBy);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.Account account = new Core.Business.Account();

                if (!reader.IsDBNull(0)) account.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) account.Status = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) account.Birthday = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) account.Gender = reader.GetDecimal(3);
                if (!reader.IsDBNull(4)) account.Name = reader.GetString(4);
                if (!reader.IsDBNull(5)) account.Telephone = reader.GetString(5);
                if (!reader.IsDBNull(6)) account.Address = reader.GetString(6);
                if (!reader.IsDBNull(7)) account.LoginName = reader.GetString(7);
                if (!reader.IsDBNull(8)) account.Password = reader.GetString(8);
                if (!reader.IsDBNull(9)) account.AvatarPath = reader.GetString(9);

                reader.Close();
                return account;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }
    }
}
